package com.hbnu.Dao.Impl;

import com.hbnu.Dao.UserDao;
import com.hbnu.Pojo.DormBuild;
import com.hbnu.Pojo.User;
import com.hbnu.Util.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public User findUserByStuCodeAndPassword(String stuCode, String password) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            String sql = "select * from tb_user where stu_code = ? and password = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, stuCode);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setStuCode(resultSet.getString("stu_code"));
                user.setDormCode(resultSet.getString("dorm_code"));
                user.setSex(resultSet.getString("sex"));
                user.setTel(resultSet.getString("tel"));
                user.setDormBuildId(resultSet.getInt("dormBuildId"));
                user.setRoleId(resultSet.getInt("role_id"));

                return  user;

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }


        return null;
    }

    @Override
    public List<User> findManager() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<User> userList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            String sql = "select * from tb_user where role_id = 1";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setSex(resultSet.getString("sex"));
                user.setTel(resultSet.getString("tel"));
                user.setDisabled(resultSet.getInt("disabled"));
                userList.add(user);
            }
            return  userList;
        }catch (Exception e) {

        }finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }

        return null;
    }

    @Override
    public List<DormBuild> findDormBuildsByUserId(Integer userId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<DormBuild> dormBuilds = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            String sql = "select tb_dormbuild.* from tb_manage_dormbuild left join tb_dormbuild on tb_manage_dormbuild.dormBuild_id = tb_dormbuild.id where tb_manage_dormbuild.user_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, userId);
            resultSet = preparedStatement.executeQuery();


            while (resultSet.next()) {
                DormBuild dormBuild = new DormBuild();
                dormBuild.setId(resultSet.getInt("id"));
                dormBuild.setName(resultSet.getString("name"));
                dormBuild.setRemark(resultSet.getString("remark"));
                dormBuild.setDisabled(resultSet.getInt("disabled"));
                dormBuilds.add(dormBuild);
            }
            return  dormBuilds;
        }catch (Exception e) {

        } finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }

        return null;
    }

    @Override
    public List<User> findManager(String searchType, String keyword) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<User> userList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            String temp = "";
            if (null != searchType && null != keyword && !"".equals(keyword)) {
                if ("name".equals(searchType)){
                    temp = " and name like '%" + keyword + "%'";
                }
                else if("sex".equals(searchType)) {
                    temp = " and sex like '%" + keyword + "%'";
                }
                else if("tel".equals(searchType)) {
                    temp = " and tel like '%" + keyword + "%'";
                }
            }


            String sql = "select * from tb_user where role_id = 1";
            if (null != temp && !"".equals(temp)) sql += temp;

            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setSex(resultSet.getString("sex"));
                user.setTel(resultSet.getString("tel"));
                user.setDisabled(resultSet.getInt("disabled"));
                userList.add(user);
            }
            return  userList;
        }catch (Exception e) {

        }finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }

        return null;
    }

    @Override
    public String findManagerStuCodeMax() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();
            String sql = "select stu_code from tb_user where id = (select max(id) from tb_user)";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String stuCode = resultSet.getString("stu_code");
                return "00" + (Integer.parseInt(stuCode) + 1);
            }

        } catch (Exception e) {

        }finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }
        return null;


    }

    @Override
    public Integer savaManager(User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            String sql = "insert into tb_user(name, password, stu_code, sex, tel, role_id, create_user_id, disabled) value(?, ?, ?, ?, ?, ?, ?, ?)";
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getStuCode());
            preparedStatement.setString(4, user.getSex());
            preparedStatement.setString(5, user.getTel());
            preparedStatement.setInt(6, user.getRoleId());
            preparedStatement.setInt(7, user.getCreateUserId());
            preparedStatement.setInt(8, user.getDisabled());
            preparedStatement.executeUpdate();
            resultSet = preparedStatement.getGeneratedKeys();
            resultSet.next();

            return  resultSet.getInt(1);
        }catch (Exception e) {

        }finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }
        return 0;
    }

    @Override
    public User findById(Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try{
            connection = DBUtils.getConnection();
            String sql = "select * from tb_user where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                User user = new User();

                user.setId(resultSet.getInt("id"));
                user.setCreateUserId(resultSet.getInt("create_user_id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setStuCode(resultSet.getString("stu_code"));
                user.setDormCode(resultSet.getString("dorm_code"));
                user.setTel(resultSet.getString("tel"));
                user.setDisabled(resultSet.getInt("disabled"));
                user.setRoleId(resultSet.getInt("role_id"));
                user.setSex(resultSet.getString("sex"));
                return user;

            }
        }catch (Exception e) {

        }finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }

        return null;
    }

    @Override
    public void updateManager(User user, String[] dormId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            String sql = "update tb_user set name = ?, password=?, sex=?, tel=?, disabled=? where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getSex());
            preparedStatement.setString(4, user.getTel());
            preparedStatement.setInt(5, user.getDisabled());
            preparedStatement.setInt(6, user.getId());
            preparedStatement.executeUpdate();

        }catch (Exception e) {

        }finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public void updateManager(User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            String sql = "update tb_user set name = ?, password=?, sex=?, tel=?, disabled=? where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getSex());
            preparedStatement.setString(4, user.getTel());
            preparedStatement.setInt(5, user.getDisabled());
            preparedStatement.setInt(6, user.getId());
            preparedStatement.executeUpdate();

        }catch (Exception e) {

        }finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public Integer findTotalNum(String sql) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            Integer totalNum = 0;
            while (resultSet.next()) {
                totalNum ++ ;
            }
            return  totalNum;

        } catch (Exception e) {

        }finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public List<User> findStudent(String sql) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            List<User> users = new ArrayList<>();
            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setCreateUserId(resultSet.getInt("create_user_id"));
                user.setDisabled(resultSet.getInt("disabled"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setRoleId(resultSet.getInt("role_id"));
                user.setSex(resultSet.getString("sex"));
                user.setStuCode(resultSet.getString("stu_code"));
                user.setTel(resultSet.getString("tel"));
                user.setDormCode(resultSet.getString("dorm_code"));
                user.setDormBuildId(resultSet.getInt("dormBuildId"));
                DormBuild build = new DormBuild();
                build.setId(resultSet.getInt("dormBuildId"));
                build.setName(resultSet.getString("buildName"));
                build.setRemark(resultSet.getString("remark"));
                user.setDormBuild(build);
                users.add(user);

            }
            return users;
        } catch (Exception e) {

        }finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public User findByStuCode(String stuCode) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();
            String sql = "select * from tb_user where stu_code = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, stuCode);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                User user = new User();

                user.setId(resultSet.getInt("id"));
                user.setCreateUserId(resultSet.getInt("create_user_id"));
                user.setDisabled(resultSet.getInt("disabled"));
                user.setDormBuildId(resultSet.getInt("dormBuildId"));
                user.setDormCode(resultSet.getString("dorm_code"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setRoleId(resultSet.getInt("role_id"));
                user.setStuCode(resultSet.getString("stu_code"));
                user.setTel(resultSet.getString("tel"));
                user.setSex(resultSet.getString("sex"));

                return user;
            }
        }catch (Exception e) {

        }finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }

        return null;
    }

    @Override
    public void updateStudent(User studentUpdate) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtils.getConnection();
            String sql = "update tb_user set name = ?, password = ?, sex = ?, tel = ?, disabled = ?, stu_code = ?, dorm_code = ?, dormBuildId = ? where id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, studentUpdate.getName());
            preparedStatement.setString(2, studentUpdate.getPassword());
            preparedStatement.setString(3, studentUpdate.getSex());
            preparedStatement.setString(4, studentUpdate.getTel());
            preparedStatement.setInt(5, studentUpdate.getDisabled());
            preparedStatement.setString(6, studentUpdate.getStuCode());
            preparedStatement.setString(7, studentUpdate.getDormCode());
            preparedStatement.setInt(8, studentUpdate.getDormBuildId());
            preparedStatement.setInt(9, studentUpdate.getId());
            preparedStatement.executeUpdate();

        }catch (Exception e) {

        }finally {
            DBUtils.close(null, preparedStatement, connection);
        }
    }

    @Override
    public void saveStudent(User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();
            String sql = "insert into tb_user(name, password, stu_code, dorm_code, sex, tel, dormBuildId, role_id, create_user_id, disabled) values(?,?,?,?,?,?,?,?, ?, 0)";
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getStuCode());
            preparedStatement.setString(4, user.getDormCode());
            preparedStatement.setString(5, user.getSex());
            preparedStatement.setString(6, user.getTel());
            preparedStatement.setInt(7, user.getDormBuildId());
            preparedStatement.setInt(8, user.getRoleId());
            preparedStatement.setInt(9, user.getCreateUserId());
            preparedStatement.executeUpdate();

        }catch (Exception ignored) {

        }finally {
            DBUtils.close(null, preparedStatement, connection);
        }
    }

    @Override
    public User findByUserIdAndManager(String sql) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setCreateUserId(resultSet.getInt("create_user_id"));
                user.setDisabled(resultSet.getInt("disabled"));
                user.setDormBuildId(resultSet.getInt("dormBuildId"));
                user.setDormCode(resultSet.getString("dorm_code"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setRoleId(resultSet.getInt("role_id"));
                user.setStuCode(resultSet.getString("stu_code"));
                user.setTel(resultSet.getString("tel"));
                user.setSex(resultSet.getString("sex"));

                return  user;
            }
        }catch (Exception e) {

        }finally {
            DBUtils.close(null, preparedStatement, connection);
        }

        return null;
    }
}
